package protypeapp;

import java.awt.BorderLayout;
import java.awt.Dimension;
import java.awt.FlowLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Vector;

import javax.swing.*;


public class DataAppMainFrame extends JFrame {
    private final int COLUMN = 6;
    private final List<String> TITLES = Arrays.asList("CId", "Cname", "Teacher","Week", "Location", "Profession");
	private final List<String> VTITLES = Arrays.asList("课程号", "课程名", "授课教师", "授课星期", "授课地点", "专业");

    private Vector<Vector<String>> dataModel = new Vector<Vector<String>>();

    private QueryItem Cid = new QueryItem("课程号：", 5);
    private QueryItem Cname = new QueryItem("课程名：", 5);
    private QueryItem Teacher = new QueryItem("授课教师：", 5);
    private QueryItem Week = new QueryItem("授课星期：", 5);
    private QueryItem Location = new QueryItem("授课地点：", 5);
    private QueryItem Profession = new QueryItem("专业：", 5);


    private JButton queryBtn = new JButton("查询");
    private JButton saveBtn = new JButton("修改");
    private JButton insertBtn = new JButton("添加");
    private JButton deleteBtn = new JButton("删除");

    private JTextArea textarea = new JTextArea(5, 5);
    private MyTable table;
    private Connection conn;

    /**
     * 主函数，用于创建窗口，设置窗口的大小以及可视化
     * @param args
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
    	DataAppMainFrame frame = new DataAppMainFrame("学生课程表基本信息录入与查询");
        frame.connectToDB();
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        frame.setMinimumSize(new Dimension(750, 500));
        frame.setVisible(true);
        frame.setResizable(false);
    }

    /**
     * 构造函数，负责创建用户界面
     * */
    public DataAppMainFrame(String title) {
    	super(title);

        Vector<String> titles = new Vector<String>(VTITLES);
        table = new MyTable(dataModel, titles);

        table.getColumnModel().getColumn(0).setPreferredWidth(30);

        JPanel controlPanel = new JPanel();
        controlPanel.setLayout(new FlowLayout());

        controlPanel.add(Cid);
        controlPanel.add(Cname);
        controlPanel.add(Teacher);
        controlPanel.add(Week);
        controlPanel.add(Location);
        controlPanel.add(Profession);

        controlPanel.add(queryBtn);
        controlPanel.add(saveBtn);
        controlPanel.add(insertBtn);
        controlPanel.add(deleteBtn);

        controlPanel.setPreferredSize(new Dimension(0, 130));

        JPanel tablePanel = new JPanel();
        tablePanel.setLayout(new BoxLayout(tablePanel, BoxLayout.Y_AXIS));
        tablePanel.add(Box.createRigidArea(new Dimension(0, 20)));
        tablePanel.add(table.getTableHeader());
        tablePanel.add(new JScrollPane(table));

        JPanel container = new JPanel();
        container.setLayout(new BorderLayout());
        container.add(textarea, BorderLayout.NORTH);
        container.add(tablePanel, BorderLayout.CENTER);

        this.add(controlPanel, BorderLayout.NORTH);
        this.add(container, BorderLayout.CENTER);
        this.add(Box.createRigidArea(new Dimension(20, 0)), BorderLayout.WEST);
        this.add(Box.createRigidArea(new Dimension(20, 0)), BorderLayout.EAST);
        this.add(Box.createRigidArea(new Dimension(0, 20)), BorderLayout.SOUTH);

        setActionListener();
    }

    /**
     * 程序启动时，需调用该方法连接到数据库
     * 之所以不放在构造函数中，是因为这些操作可能抛出异常，需要单独处理
     * */
    public void connectToDB() throws SQLException, ClassNotFoundException {
        Class.forName("org.mariadb.jdbc.Driver");
        final String URL = "jdbc:mariadb://localhost:3307/mydb?characterEncoding=UTF-8";
        conn = DriverManager.getConnection(URL, "root", "123456");
    }

    /**
     * 设置事件监听事件
     */
    private void setActionListener() {
        //根据指定条件，列出数据库中满足条件的记录
        queryBtn.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                ArrayList<String> conditions = new ArrayList<String>();

                if (Cid.isSelected()) { conditions.add("(Cid = '" + Cid.getText() + "')");  }
                if (Cname.isSelected()) {   conditions.add("(Cname like '" + Cname.getText() + "')");   }
                if (Teacher.isSelected()) { conditions.add("(Teacher = '" + Teacher.getText() + "')"); }
                if (Week.isSelected()) {    conditions.add("(Week = '" + Week.getText() + "')");    }
                if (Location.isSelected()) {    conditions.add("(Location = '" + Location.getText() + "')");    }
                if (Profession.isSelected()) {  conditions.add("(Profession like '" + Profession.getText() + "')"); }

                StringBuilder sb = new StringBuilder();
                sb.append("select * from class");
                int length = conditions.size();
                if (length != 0) {  sb.append(" where ");   }
                for (int i = 0; i < length; i++) {
                    sb.append(conditions.get(i));
                    if (i != length - 1) sb.append(" AND ");
                }
                sb.append(";");
                String queryString = sb.toString();
                textarea.setText(queryString);

                dataModel.clear();
                Statement stmt;
                try {
                    stmt = conn.createStatement();
                    ResultSet rs = stmt.executeQuery(queryString);
                    Vector<String> record;
                    while (rs.next()) {
                        record = new Vector<String>();
                        for (int i = 0; i < COLUMN; i++) {
                            record.add(rs.getString(i + 1));
                        }
                        dataModel.add(record);
                    }
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }

                //更新表格
                table.validate();
                table.updateUI();
            }
        });

        //根据用户当前选中的单元格，修改数据库中对应记录的对应字段
        saveBtn.addActionListener(new ActionListener() {

            @Override
            public void actionPerformed(ActionEvent e) {
                int row = table.getSelectedRow();
                int column = table.getSelectedColumn();
                if (row == -1 || column == 0) { return; }

                String val = dataModel.get(row).get(column);
                String cid = dataModel.get(row).get(0);
                String sql = "update class set " + TITLES.get(column) + " = ? where Cid = ?;";

                //在文本框显示 SQL 命令
                String cmd = "update class set " + TITLES.get(column) + " = ";
                cmd += (TITLES.get(column) == "Cid") ? val : "'" + val + "'";
                cmd += " where Cid = '" + cid + "';";
                textarea.setText(cmd);

                PreparedStatement ps;
                try {
                    ps = conn.prepareStatement(sql);
                    if (TITLES.get(column) == "Time") {
                        ps.setInt(1, Integer.valueOf(val));
                    }
                    else {
                        ps.setString(1, val);
                    }
                    ps.setString(2, cid);
                    ps.executeUpdate();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        });

        //往数据库中插入一条新的记录
        insertBtn.addActionListener(new ActionListener() {

            @Override
            public void actionPerformed(ActionEvent e) {
                String sql = "insert into class values (?,?,?,?,?,?);";
                String cid = Cid.getText();
                String cname = Cname.getText();
                String teacher = Teacher.getText();
                String time = Week.getText();
                String location = Location.getText();
                String profession = Profession.getText();


                //在文本框显示 SQL 命令
                String cmd = "insert into class values ('" + cid + "', '" + cname + "', '" + teacher + "', '" + time + "', '" + location + "', '" + profession  + "');";
                textarea.setText(cmd);

                PreparedStatement ps;
                try {
                    ps = conn.prepareStatement(sql);
                    ps.setString(1, cid);
                    ps.setString(2, cname);
                    ps.setString(3, teacher);
                    ps.setString(4, time);
                    ps.setString(5, location);
                    ps.setString(6, profession);
                    ps.executeUpdate();
                    dataModel.add(new Vector<String>(Arrays.asList(cid, cname, teacher, time, location, profession)));

                    //更新表格
                    table.validate();
                    table.updateUI();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }

        });

        //将用户当前选中的记录从数据库中删除
        deleteBtn.addActionListener(new ActionListener() {

            @Override
            public void actionPerformed(ActionEvent e) {
                int row = table.getSelectedRow();
                String cid = dataModel.get(row).get(0);
                String sql = "delete from class where Cid = '" + cid + "';";

                //在文本框显示 SQL 命令
                textarea.setText(sql);

                Statement stmt;
                try {
                    stmt = conn.createStatement();
                    if (stmt.executeUpdate(sql) == 0) { return; }
                    dataModel.remove(row);

                    //更新表格
                    table.validate();
                    table.updateUI();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        });
    }
}

/** 查询项目
 * 将复选框、标签、文本框组合成一个组件
 * 对外提供获取文本和选中状态的两个方法
 */
class QueryItem extends JPanel {
    private JCheckBox checkbox;
    private JLabel label;
    private JTextField textfield;

    public QueryItem(String labelText, int textWidth) {
        checkbox = new JCheckBox();
        label = new JLabel(labelText);
        textfield = new JTextField(textWidth);
        this.add(checkbox);
        this.add(label);
        this.add(textfield);
    }

    public boolean isSelected() {
        return checkbox.isSelected();
    }

    public String getText() {
        return textfield.getText();
    }
}


/** 表格组件
 * 重载了 JTable 的 isCellEditable 方法
 * 目的是防止编辑 Sid 字段，禁止修改主键
 */
class MyTable extends JTable {
    public MyTable(Vector data, Vector title) {
        super(data, title);
    }

    @Override
    public boolean isCellEditable(int row, int column) {
        if (column == 0) return false;
        else return true;
    }
}